<?php

namespace App\Http\Controllers\Home;
use Illuminate\Http\Request;
use App\Http\Requests;
use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Controller;
use Illuminate\Database\QueryException;
use Maatwebsite\Excel\Facades\Excel;
// use Excel;
//导入导出表格
class ExcelController extends Controller
{
    //Excel文件导出功能
    public function export(){
        $cellData = [
            ['nik','user_name','phone'],
            ['10001','AAAAA','99'],
            ['10002','BBBBB','92'],
            ['10003','CCCCC','95'],
            ['10004','DDDDD','89'],
            ['10005','EEEEE','96'],
        ];
        Excel::create('paymentdata',function($excel) use ($cellData){
            $excel->sheet('score', function($sheet) use ($cellData){
                $sheet->rows($cellData);
            });
        })->export('xls');
    }
    //Excel文件导出功能
    public function loadExcel()
    {   echo memory_get_usage();
        echo '<br/>';
        ini_set('memory_limit','500M');
        set_time_limit(0);//设置超时限制为0分钟
        
        //文件路径
        $filePath = 'C:/Users/need/Desktop/Blacklist/2/'.iconv('UTF-8', 'GBK', 'BL index2_result_20181117').'.csv';  
        Excel::load($filePath, function($reader) {
                $data = $reader->all();  
                //laravel可以直接转换为数组  
                $data = $data->toArray();
                $arr = [];
                $arr2 = [];
                $file_e = 1;//1为csv文件 2为Excel
                if($file_e == 1){
                    $chunk_result = array_chunk($data, 10000);
                    foreach($chunk_result as $k => $val){
                        foreach($val as $key => $value){
                            $v = explode('|',$value['risk_typerisk_itemrisk_valuereasonreport_timereview_timeoperatorreview_status']);
                            if($v[1] == 'Identification number' && is_numeric($v[2])){
                                $arr2[$key]['nik'] = $v[2];
                                $arr2[$key]['black_date'] = $v[4];
                                $arr2[$key]['add_time'] = date('Y-m-d H:i:s');
                            }else if($v[1] == 'Mobile number'  && is_numeric($v[2])){
                                $arr[$key]['phone'] = $v[2];
                                $arr[$key]['black_date'] = $v[4];
                                $arr[$key]['add_time'] = date('Y-m-d H:i:s');
                            }else{
                                unset($value);
                            }
                            unset($value);
                        }
                        
                        DB::table('blacklist_data2')->insert($arr);
                        DB::table('blacklist_data2')->insert($arr2);
                        unset($val);
                    }
                }else{
                    foreach($data as $k=>$val){
                        $arr[$k]['phone'] = $val['phone'];
                        $arr[$k]['name'] = empty($val['user_name']) ? '' : $val['user_name'];
                        $arr[$k]['nik'] = empty($var['nik']) ? '' : $var['nik']; 
                        $arr[$k]['black_date'] = $val['overdue_begin_time'];
                        $arr[$k]['add_time'] = date('Y-m-d H:i:s');
                        unset($val);
                        unset($k);
                    }
                    $chunk_result = array_chunk($arr, 10000);
                    foreach($chunk_result as $value){
                        DB::table('blacklist_data2')->insert($value);
                        unset($value);
                    }
                }
                echo memory_get_usage();//查看内存
                //导入的数据你可根据自己的情况插入数据库
                //DB::table('blacklist_data2')->insert($arr);
                // dd($data);  
        }); 
    }
}
